/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
/*
 *@author Nguyen Thanh Ha
 */
package Business;

import Objects.Vendor;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author HaKutu
 */
public class VendorBusiness {

    /**
     * Method getAllVendor() used to get all Vendor from database
     *
     * @return ArrayList<Vendor>
     * @throws java.sql.SQLException
     */
    public static ArrayList<Vendor> getAllVendor() throws SQLException {
        ArrayList<Vendor> list = new ArrayList<>();

        String sql = "{call sp_getAllVendor}";

        try {
            ResultSet rs = DBAccess.DB_Connect.executeQuery(sql, null);
            while (rs.next()) {
                Vendor ven = new Vendor();
                ven.setVendorID(rs.getString("VendorID"));
                ven.setVendorName(rs.getString("VendorName"));
                ven.setVendorPhone(rs.getString("VendorPhone"));
                ven.setVendorAddress(rs.getString("VendorAddress"));
                ven.setVendorEmail(rs.getString("VendorEmail"));

                list.add(ven);
            }

        } catch (SQLException ex) {
            Logger.getLogger(VendorBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(VendorBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
    }

    /**
     * method used to add new Vendor
     */
    public static int addVendor(Vendor ven) throws SQLException {
        int kt = 0;
        Connection conn = null;
        try {
            conn = DBAccess.DB_Connect.getConnection();
            String sql = "{call sp_insertVendor(?,?,?,?,?)}";
            CallableStatement csm = conn.prepareCall(sql);
            csm.setString(1, ven.getVendorID());
            csm.setString(2, ven.getVendorName());
            csm.setString(3, ven.getVendorPhone());
            csm.setString(4, ven.getVendorAddress());
            csm.setString(5, ven.getVendorEmail());
            kt = csm.executeUpdate();
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(VendorBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(VendorBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
        return kt;
    }

    public static int updateVendor(String venID, String VenName, String venPhone, String venAdd, String venEmail) {
        int result = 0;
        Connection cnn = null;
        try {

            cnn = DBAccess.DB_Connect.getConnection();

            String sql = "{call sp_updateVendor(?,?,?,?,?)}";
            CallableStatement csm = cnn.prepareCall(sql);
            csm.setString(1, venID);
            csm.setString(2, VenName);
            csm.setString(3, venPhone);
            csm.setString(4, venAdd);
            csm.setString(5, venEmail);

            result = csm.executeUpdate();

        } catch (ClassNotFoundException ex) {
            Logger.getLogger(VendorBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(VendorBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cnn != null) {
                try {
                    cnn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(VendorBusiness.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return result;
    }

    /**
     * method used to get Vendor by Name
     */
    public static ArrayList<Vendor> getVendorByName(String name) {

        ArrayList<Vendor> list = new ArrayList<>();
        String sql = "{call sp_getVendorName(?)}";
        ArrayList list1 = new ArrayList();
        list1.add(name);
        try {
            ResultSet rs = DBAccess.DB_Connect.executeQuery(sql, list1);
            while (rs.next()) {
                Vendor ven = new Vendor();
                ven.setVendorID(rs.getString("VendorID"));
                ven.setVendorName(rs.getString("VendorName"));
                ven.setVendorPhone(rs.getString("VendorPhone"));
                ven.setVendorAddress(rs.getString("VendorAddress"));
                ven.setVendorEmail(rs.getString("VendorEmail"));

                list.add(ven);
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(VendorBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(VendorBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }

        return list;

    }

    /**
     * method used to get Vendor by ID
     */
    public static ArrayList<Vendor> getVendorbyID(String ID) {
        ArrayList<Vendor> list = new ArrayList<>();

        String sql = "{call sp_getVendorID(?)}";
        ArrayList list1 = new ArrayList();
        list1.add(ID);
        try {
            ResultSet rs = DBAccess.DB_Connect.executeQuery(sql, list1);
            while (rs.next()) {
                Vendor ven = new Vendor();
                ven.setVendorID(rs.getString("VendorID"));
                ven.setVendorName(rs.getString("VendorName"));
                ven.setVendorPhone(rs.getString("VendorPhone"));
                ven.setVendorAddress(rs.getString("VendorAddress"));
                ven.setVendorEmail(rs.getString("VendorEmail"));

                list.add(ven);
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(VendorBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(VendorBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }

        return list;

    }

 /**
     * method used to get Vendor by Phone
     */
    public static ArrayList<Vendor> getVendorbyPhone(String Phone) {
        ArrayList<Vendor> list = new ArrayList<>();

        String sql = "{call sp_getVendorID(?)}";
        ArrayList list1 = new ArrayList();
        list1.add(Phone);
        try {
            ResultSet rs = DBAccess.DB_Connect.executeQuery(sql, list1);
            while (rs.next()) {
                Vendor ven = new Vendor();
                ven.setVendorID(rs.getString("VendorID"));
                ven.setVendorName(rs.getString("VendorName"));
                ven.setVendorPhone(rs.getString("VendorPhone"));
                ven.setVendorAddress(rs.getString("VendorAddress"));
                ven.setVendorEmail(rs.getString("VendorEmail"));

                list.add(ven);
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(VendorBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(VendorBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }

        return list;

    }
     /**
     * method used to get Vendor by Address
     */
    public static ArrayList<Vendor> getVendorbyAddress(String add) {
        ArrayList<Vendor> list = new ArrayList<>();

        String sql = "{call sp_getVendorID(?)}";
        ArrayList list1 = new ArrayList();
        list1.add(add);
        try {
            ResultSet rs = DBAccess.DB_Connect.executeQuery(sql, list1);
            while (rs.next()) {
                Vendor ven = new Vendor();
                ven.setVendorID(rs.getString("VendorID"));
                ven.setVendorName(rs.getString("VendorName"));
                ven.setVendorPhone(rs.getString("VendorPhone"));
                ven.setVendorAddress(rs.getString("VendorAddress"));
                ven.setVendorEmail(rs.getString("VendorEmail"));

                list.add(ven);
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(VendorBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(VendorBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }

        return list;

    }
    /**
     * method used to delete Vendor
     */
    public static int deleteVendor(String venID) {
        int rs = 0;
        String sql = "{call sp_deleteVendor(?)}";
        ArrayList list = new ArrayList();
        list.add(venID);
        try {
            rs = DBAccess.DB_Connect.executeNonQueryPreparedStatement(sql, list);
        } catch (SQLException ex) {
            Logger.getLogger(VendorBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(VendorBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rs;
    }

    /**
     * method get newest vendor
     *
     */
    public static Vendor getNewestVendor() {
        Vendor ven = new Vendor();

        String sql = "{call sp_getNewestVendor}";
        try {
            ResultSet rs = DBAccess.DB_Connect.executeQuery(sql, null);

            if (rs.next()) {
                ven.setVendorID(rs.getString("VendorID"));
                ven.setVendorName(rs.getString("VendorName"));
                ven.setVendorPhone(rs.getString("VendorPhone"));
                ven.setVendorAddress(rs.getString("VendorAddress"));
                ven.setVendorEmail(rs.getString("VendorEmail"));
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(VendorBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(VendorBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }

        return ven;
    }
        /**
     * method check the existence of VendorName field
     */
    public static int checkVendorName(String venName) {
        int kt = 0;

        String sql = "{call sp_getVendorByName(?)}";
        ArrayList list = new ArrayList();
        list.add(venName);
        try {
            ResultSet rs = DBAccess.DB_Connect.executeQuery(sql, list);
            if (rs.next()) {
                kt = 1;
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(VendorBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(VendorBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }


        return kt;
    }
}

   